﻿create database ResultDeclarationBoard
go

use ResultDeclarationBoard
go

CREATE TABLE groups (

  group_id int IDENTITY(1,1) NOT NULL,
  group_name nvarchar(50) NOT NULL,
  group_desc nvarchar(200) DEFAULT NULL,
  PRIMARY KEY (group_id)
);
GO

CREATE TABLE users (
  [user_id] int NOT NULL IDENTITY,
  username varchar(50) NOT NULL,
  first_name nvarchar(20) DEFAULT NULL,
  last_name nvarchar(20) DEFAULT NULL,
  groupId int references groups(group_id)
  password VARCHAR(50) NOT NULL,
  PRIMARY KEY ([user_id])
);
GO



/*ALTER TABLE users
ADD groupId int references groups(group_id)
*/

CREATE TABLE user_groups (
  user_id int NOT NULL,
  group_id int NOT NULL,
  PRIMARY KEY (user_id,group_id),
  CONSTRAINT fk_groups FOREIGN KEY (group_id) REFERENCES groups (group_id),
  CONSTRAINT fk_users FOREIGN KEY (user_id) REFERENCES users (user_id)
); 
GO

create table courses(
	Id int identity(1,1) primary key,
	CourseName varchar(255)
)

create table Semesters(
	Id int identity(1,1) primary key,
	SemesterName varchar(255)
)

create table Classes(
	Id int identity(1,1) primary key,
	ClassName varchar(255),
	CourseId int references courses(Id)
)

create table Students(
	Id int identity(1,1) primary key,
	enRoll varchar(10),
	stuName varchar(255),
	stuPhone varchar(255),
	stuAddress varchar(255),
	classId int references Classes(Id),
	stuEmail varchar(255),
	status bit
)


create table Subjects
(
	Id int identity(1,1) primary key,
	suName varchar(50),
	suSemester int references Semesters(Id),
	MinMark float,
	MaxMark float
)
go

CREATE TABLE Subject_Fac
(
	subId int references Subjects(Id),
	userId int references users(user_id),
)

create table Exams(
	Id int identity(1,1) primary key,
	DateCreate datetime,
	ExamId varchar(10),
	assignFac int references users(user_id),
	DateStart datetime,
	DateEnd datetime,
	DateReCounting datetime,
	SubjectId int references Subjects(Id),
	status int
)

create table student_exams(
	Id int identity(1,1) primary key,
	examId int references Exams(Id),
	studentId int references Students(Id),
	mark float,
	remark float,
	status bit
)
go

create table Registers
(
	reId int identity(1,1) primary key,
	Id int foreign key references student_exams(Id),
	CreateDate Datetime,
	assignFac int references users(user_id),
	status bit
)
go

CREATE TABLE [Messages]
(
	Id int identity(1,1) primary key,
	registerId int foreign key references Registers(reId),
	examId int foreign key references Exams(Id),
	userId int references users(user_id),
	[messages] NTEXT,
	status bit
)
GO

INSERT  INTO groups(group_name,group_desc) VALUES 
  ('Faculty','Faculty'),
  ('Administration','Administration');
GO  
INSERT INTO users(username,first_name,last_name,password) VALUES 
  ('john','John','Doe','6e0b7076126a29d5dfcbd54835387b7b'), /*john123*/
  ('admin',NULL,NULL,'21232f297a57a5a743894a0e4a801fc3'); /*admin*/
GO
INSERT  INTO user_groups(user_id,group_id) VALUES (1,1),(2,1),(2,2);
GO

INSERT INTO courses VALUES (N'HDSE')
INSERT INTO courses VALUES (N'ITT')
GO


INSERT INTO Classes VALUES ('C0912L',1)
INSERT INTO Classes VALUES ('C0808G',2)
GO

INSERT INTO Semesters VALUES ('Sem 1')
INSERT INTO Semesters VALUES ('Sem 2')
INSERT INTO Semesters VALUES ('Sem 3')
INSERT INTO Semesters VALUES ('Sem 4')
GO

INSERT INTO Students VALUES ('0000000001','Tran Van Bich','0973582890','BG',1,'tranbich308@gmail.com',1)
INSERT INTO Students VALUES ('0000000002','Tran Van A','0973582890','BG',2,'test@gmail.com',1)
GO

INSERT INTO Subjects VALUES ('Core Java',1,0,25)
INSERT INTO Subjects VALUES ('RDBMS',1,0,25)
INSERT INTO Subjects VALUES ('WCF',2,0,25)
INSERT INTO Subjects VALUES ('EJB',4,0,25)
INSERT INTO Subjects VALUES ('XML',3,0,25)
GO

INSERT INTO Subject_Fac VALUES (1,2)
INSERT INTO Subject_Fac VALUES (2,2)
INSERT INTO Subject_Fac VALUES (4,2)
GO

INSERT INTO Exams(DateCreate,ExamId,assignFac,DateStart,DateEnd,DateReCounting,SubjectId,status) VALUES(GETDATE(),'Exam000001',2,DATEADD(DD,2,GETDATE()),DATEADD(DD,5,GETDATE()),DATEADD(DD,15,GETDATE()),1,1)
GO
	Id int identity(1,1) primary key,
	examId int references Exams(Id),
	studentId int references Students(Id),
	mark float,
	remark float,
	status bit
/*
INSERT INTO student_exams VALUES(1,3,0,0,0)
INSERT INTO student_exams VALUES(1,4,0,0,0)
*/
SELECT * FROM Students
SELECT * FROM courses
SELECT * FROM users
SELECT * FROM groups 
SELECT * FROM Subjects
SELECT * FROM users where user_id in ( SELECT userId FROM Subject_Fac where subId = 1)
select * from Exams
 select * from student_exams
 SELECT * FROM Subject_Fac
 SELECT * FROM Students
 SELECT s.*,e.* FROM Student_Exams s, Exams e WHERE s.studentId = 3 and s.examId = e.Id and DateEnd > GETDATE()
 SELECT * FROM student_exams
 SELECT * FROM Registers
 SELECT * FROM Messages WHERE userId is null
 SELECT * FROM users
 
 